﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using ReportWizard.Helpers;
using ReportWizard.Interface;

namespace ReportWizard.DataAccess
{
    public class BookmarkGroup : IBookmark, IComparer<BookmarkGroup>
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime DateCreated { get; set; }
        public bool Deleted { get; set; }

        public List<WordBookmark> GroupBookmarks { get; set; }

        public void Insert()
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand saveCmd = new SqlCommand("BookmarkGroupInsert", con);
                    saveCmd.CommandType = CommandType.StoredProcedure;

                    saveCmd.Parameters.Add("@Name", SqlDbType.VarBinary).Value = Crypto.Encrypt(Name);
                    saveCmd.Parameters.Add("@Description", SqlDbType.VarBinary).Value = Crypto.Encrypt(!string.IsNullOrEmpty(Description) ? Description : string.Empty);
                    saveCmd.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;

                    con.Open();
                    bool isRowsAffected = saveCmd.ExecuteNonQuery() > 0;
                    if (isRowsAffected)
                    {
                        Id = Convert.ToInt32(saveCmd.Parameters["@Id"].Value);
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
            }
        }

        public bool Update()
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand updCmd = new SqlCommand("BookmarkGroupUpdate", con);
                    updCmd.CommandType = CommandType.StoredProcedure;

                    updCmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
                    updCmd.Parameters.Add("@Name", SqlDbType.VarBinary).Value = Crypto.Encrypt(Name);
                    updCmd.Parameters.Add("@Description", SqlDbType.VarBinary).Value = Crypto.Encrypt(!string.IsNullOrEmpty(Description) ? Description : string.Empty);

                    con.Open();
                    bool isRowsAffected = updCmd.ExecuteNonQuery() > 0;
                    if (isRowsAffected)
                    {
                        HistoryRecord hr = new HistoryRecord
                        {
                            Action = HistoryEvent.BookmarkChange,
                            Description = string.Format("Переименована группа закладок {0}", Name)
                        };
                        hr.Insert();
                    }

                    return isRowsAffected;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        public static bool Delete(int id, int wordTemplateId)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand delCmd = new SqlCommand("BookmarkGroupDelete", con);
                    delCmd.CommandType = CommandType.StoredProcedure;

                    delCmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
                    delCmd.Parameters.Add("@WordTemplateId", SqlDbType.Int).Value = wordTemplateId;

                    con.Open();
                    bool isRowsAffected = delCmd.ExecuteNonQuery() > 0;
                    if (isRowsAffected)
                    {
                        BookmarkGroup bg = Select(id);
                        if (bg != null)
                        {
                            
                                HistoryRecord hr = new HistoryRecord
                                {
                                    Action = HistoryEvent.BookmarkChange,
                                    Description = string.Format("Разгруппирована группа закладок {0}", bg.Name)
                                };
                                hr.Insert();
                            
                        }
                    }

                    return isRowsAffected;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        public static BookmarkGroup Select(int id)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand("BookmarkGroupSelect", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

                    con.Open();
                    List<BookmarkGroup> bList = MapFrom(getCmd.ExecuteReader());

                    if (bList != null)
                    {
                        BookmarkGroup bg = bList.FirstOrDefault();
                        if (bg != null)
                        {
                            bg.GroupBookmarks = WordBookmark.SelectInGroup(id);
                        }

                        return bg;
                    }
                    return null;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        public static List<BookmarkGroup> SelectInTemplate(int templateId)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand("BookmarkGroupSelectByTemplateId", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@WordTemplateId", SqlDbType.Int).Value = templateId;

                    con.Open();
                    List<BookmarkGroup> bList = MapFrom(getCmd.ExecuteReader());
                    if (bList != null)
                    {
                        for (int i = 0; i < bList.Count; i++)
                        {
                            bList[i].GroupBookmarks = WordBookmark.SelectInGroup(bList[i].Id);
                        }
                        return bList;
                    }
                    return null;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        private static List<BookmarkGroup> MapFrom(SqlDataReader dr)
        {
            try
            {
                if (dr != null && dr.HasRows)
                {
                    List<BookmarkGroup> acList = new List<BookmarkGroup>();
                    while (dr.Read())
                    {
                        BookmarkGroup wt = new BookmarkGroup
                        {
                            Id = Convert.ToInt32(dr["Id"]),
                            Name = Crypto.Decrypt((byte[])dr["Name"]),
                            Description = Crypto.Decrypt((byte[])dr["Description"]),
                            DateCreated = Convert.ToDateTime(dr["DateCreated"]),
                            Deleted = Convert.ToBoolean(dr["Deleted"])
                        };
                        
                        acList.Add(wt);
                    }
                    acList.Sort(new BookmarkGroup());
                    return acList;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        public int Compare(BookmarkGroup x, BookmarkGroup y)
        {
            return x.Name.CompareTo(y.Name);
        }
    }
}